﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NetDon.OA.Data.HelperClasses;
using NetDon.OA.Interface;
using NetDon.OA.Interface.eLogistics;
using NetDon.Utilities;

namespace NetDon.OA.Business.eLogitics
{
    public class ReportQueryService : IReportQueryService
    {
        private const string SP_QueryIsuingOrderQuery = "QueryIssuingOrder";
        private const string SP_QueryIssuingOrderDeliveryQuery = "QueryIssuingOrderDelivery";

        private const string SP_QueryShuntingTrackInfoQuery = "QueryShuntingTrackInfo";
        private const string SP_QueryOnsiteEntrucking = "QueryOnsiteEntrucking";
        private const string SP_QueryOnwayTrackQuery = "QueryOnwayTrack";

        private const string SP_QueryReceiptInfoTrackQuery = "QueryReceiptInfoTrack";
        private const string SP_QueryPaymentMachineAccount = "QueryPaymentMachineAccount";
        private const string SP_QueryPaymentMachineAccountDetails = "QueryPaymentMachineAccountDetails";

        private const string SP_QueryPaymentMachineAccountDeduction = "QueryPaymentMachineAccountDeduction";
        

        private const string SP_QueryGoodsShippingException = "GoodsShippingExceptionQuery";

 

        private const string SP_QueryReceiptInfoQuery = "ReceiptInfoQuery";

        private const string SP_GoodsShippingStatusQuery = "GoodsShippingStatusQuery";

        private const string SP_GoodsShippingOnWayTrackSummary = "GoodsShippingOnWayTrackSummary";

        private const string SP_QueryIssuingOrderGather = "QueryIssuingOrderGather";//JamesRen



        public System.Data.DataTable QueryGoodsShippingOnWayTrackSummary(GoodsShippingStatusQueryCriteria criteria, string tableName)
        {
            var parameters = GetGoodsShippingStatusQuerySqlParameters(criteria);

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_GoodsShippingOnWayTrackSummary, parameters, toReturn, null);

            return toReturn;
        }



        public System.Data.DataTable QueryReceiptInfoQuery(ReceiptInfoQueryCriteria criteria, string tableName)
        {
            var parameters = GetQueryReceiptInfoQuerySqlParameters(criteria);

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryReceiptInfoQuery, parameters, toReturn, null);

            return toReturn;
        }



  

        public System.Data.DataTable QueryGoodsShippingExceptionQuery(GoodsShippingExceptionQueryCriteria criteria, string tableName)
        {
            var parameters = GetQueryGoodsShippingExceptionSqlParameters(criteria);

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryGoodsShippingException, parameters, toReturn, null);

            return toReturn;
        }
        public System.Data.DataTable QueryIsuingOrderQuery(IssuingOrderSearchCriteria criteria, string tableName)
        {

            var parameters = GetIssuingOrderSearchSqlParameters(criteria);

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryIsuingOrderQuery, parameters, toReturn, null);

            return toReturn;


        }


        public System.Data.DataTable QueryPaymentMachineAccount(MachineAccountSearchReportCriteria criteria,
                                                       string tableName)
        {

            var parameters = GetPaymentAccountQuerySearchSqlParameters(criteria);

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryPaymentMachineAccount, parameters, toReturn, null);

            return toReturn;


        }

        public System.Data.DataTable QueryPaymentMachineAccountDetails(MachineAccountSearchReportCriteria criteria,
                                                       string tableName)
        {

            var parameters = GetPaymentAccountDetailsQuerySearchSqlParameters(criteria);

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryPaymentMachineAccountDetails, parameters, toReturn, null);

            return toReturn;


        }

        
        public System.Data.DataTable QueryPaymentMachineAccountDeduction(MachineAccountSearchCriteria criteria,
                                                       string tableName)
        {

            var parameters = GetQueryPaymentMachineAccountDeductionSearchSqlParameters(criteria);

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryPaymentMachineAccountDeduction, parameters, toReturn, null);

            return toReturn;


        }


        

        public System.Data.DataTable QueryReceiptInfoTrackQuery(IssuingOrderReceiptInfoTrackSearchCriteria criteria, string tableName)
        {

            var parameters = GetIssuingOrderSearchSqlParameters(new IssuingOrderSearchCriteria()
            {
                OrderCode = criteria.OrderCode,
                CustomerId = criteria.CustomerId,
                TransportationPlanCode = criteria.TransportationPlanCode,
                OrderStatus = criteria.OrderStatus,
                RouteTypeId = criteria.RouteTypeId,
                ReceivingOrderDate = criteria.ReceivingOrderDate,
                GoodsShippedTypeId = criteria.GoodsShippedTypeId,
                DeliveryCity = criteria.DeliveryCity,
                AreaId = criteria.AreaId
            });

            Array.Resize(ref parameters, parameters.Length + 5);

            parameters[parameters.Length - 5] = new SqlParameter("@IsReceived", SqlDbType.Bit);
            if (!criteria.IsReceived.HasValue)
            {
                parameters[parameters.Length - 5].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 5].Value = criteria.IsReceived.Value;
            }
            parameters[parameters.Length - 4] = new SqlParameter("@ReceiptInfoPlannedResponseDateStart", SqlDbType.DateTime);
            if (criteria.ReceiptInfoPlannedResponseDate == null || !criteria.ReceiptInfoPlannedResponseDate.Start.HasValue ||
                criteria.ReceiptInfoPlannedResponseDate.Start == DateTime.MinValue || criteria.ReceiptInfoPlannedResponseDate.Start == DateTime.MaxValue)
            {
                parameters[parameters.Length - 4].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 4].Value = criteria.ReceiptInfoPlannedResponseDate.Start.Value;
            }

            parameters[parameters.Length - 3] = new SqlParameter("@ReceiptInfoPlannedResponseDateEnd", SqlDbType.DateTime);
            if (criteria.ReceiptInfoPlannedResponseDate == null || !criteria.ReceiptInfoPlannedResponseDate.End.HasValue ||
                criteria.ReceiptInfoPlannedResponseDate.End == DateTime.MinValue || criteria.ReceiptInfoPlannedResponseDate.End == DateTime.MaxValue)
            {
                parameters[parameters.Length - 3].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 3].Value = criteria.ReceiptInfoPlannedResponseDate.End.Value;
            }


            parameters[parameters.Length - 2] = new SqlParameter("@IsReceiptInfoDelayed", SqlDbType.Bit);
            if (!criteria.IsReceiptInfoDelayed.HasValue)
            {
                parameters[parameters.Length - 2].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 2].Value = criteria.IsReceiptInfoDelayed;
            }

            parameters[parameters.Length - 1] = new SqlParameter("@IsReceiptInfoExceptional", SqlDbType.Bit);
            if (!criteria.IsReceiptInfoExceptional.HasValue)
            {
                parameters[parameters.Length - 1].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 1].Value = criteria.IsReceiptInfoExceptional.Value;
            }


            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryReceiptInfoTrackQuery, parameters, toReturn, null);

            return toReturn;


        }

        public System.Data.DataTable QueryOnwayTrackQuery(IssuingOrderOnWayTrackSearchCriteria criteria, string tableName)
        {

            var parameters = GetIssuingOrderSearchSqlParameters(new IssuingOrderSearchCriteria()
                                                                    {
                                                                        OrderCode = criteria.OrderCode,
                                                                        CustomerId = criteria.CustomerId,
                                                                        TransportationPlanCode = criteria.TransportationPlanCode,
                                                                        OrderStatus = criteria.OrderStatus,
                                                                        RouteTypeId = criteria.RouteTypeId,
                                                                        ReceivingOrderDate = criteria.ReceivingOrderDate,
                                                                        GoodsShippedTypeId = criteria.GoodsShippedTypeId,
                                                                        DeliveryCity = criteria.DeliveryCity,
                                                                        AreaId = criteria.AreaId
                                                                    });

            Array.Resize(ref parameters, parameters.Length + 7);
            parameters[parameters.Length - 7] = new SqlParameter("@IsTracked", SqlDbType.Bit);
            if (!criteria.IsTracked.HasValue)
            {
                parameters[parameters.Length - 7].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 7].Value = criteria.IsTracked.Value ;
            }
            parameters[parameters.Length - 6] = new SqlParameter("@PlannedGoodsDeliveredDateStart", SqlDbType.DateTime);
            if (criteria.PlannedGoodsDeliveredDate == null || !criteria.PlannedGoodsDeliveredDate.Start.HasValue ||
                criteria.PlannedGoodsDeliveredDate.Start == DateTime.MinValue || criteria.PlannedGoodsDeliveredDate.Start == DateTime.MaxValue)
            {
                parameters[parameters.Length - 6].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 6].Value = criteria.PlannedGoodsDeliveredDate.Start.Value;
            }

            parameters[parameters.Length - 5] = new SqlParameter("@PlannedGoodsDeliveredDateEnd", SqlDbType.DateTime);
            if (criteria.PlannedGoodsDeliveredDate == null || !criteria.PlannedGoodsDeliveredDate.End.HasValue ||
                criteria.PlannedGoodsDeliveredDate.End == DateTime.MinValue || criteria.PlannedGoodsDeliveredDate.End == DateTime.MaxValue)
            {
                parameters[parameters.Length - 5].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 5].Value = criteria.PlannedGoodsDeliveredDate.End.Value;
            }


            parameters[parameters.Length - 4] = new SqlParameter("@ActualDeliveredDateStart", SqlDbType.DateTime);
            if (criteria.ActualDeliveredDate == null || !criteria.ActualDeliveredDate.Start.HasValue ||
                criteria.ActualDeliveredDate.Start == DateTime.MinValue || criteria.ActualDeliveredDate.Start == DateTime.MaxValue)
            {
                parameters[parameters.Length - 4].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 4].Value = criteria.ActualDeliveredDate.Start.Value;
            }

            parameters[parameters.Length - 3] = new SqlParameter("@ActualDeliveredDateEnd", SqlDbType.DateTime);
            if (criteria.ActualDeliveredDate == null || !criteria.ActualDeliveredDate.End.HasValue ||
                criteria.ActualDeliveredDate.End == DateTime.MinValue || criteria.ActualDeliveredDate.End == DateTime.MaxValue)
            {
                parameters[parameters.Length - 3].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 3].Value = criteria.ActualDeliveredDate.End.Value;
            }


            parameters[parameters.Length - 2] = new SqlParameter("@IsGoodsDelayed", SqlDbType.Bit);
            if (!criteria.IsGoodsDelayed.HasValue)
            {
                parameters[parameters.Length - 2].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 2].Value = criteria.IsGoodsDelayed;
            }

            parameters[parameters.Length - 1] = new SqlParameter("@IsGoodsExceptional", SqlDbType.Bit);
            if (!criteria.IsGoodsExceptional.HasValue)
            {
                parameters[parameters.Length - 1].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 1].Value = criteria.IsGoodsExceptional.Value;
            }


            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryOnwayTrackQuery, parameters, toReturn, null);

            return toReturn;


        }
        public System.Data.DataTable QueryOnsiteEntruckingQuery(IssuingOrderSearchCriteria criteria, string tableName)
        {

            var parameters = GetIssuingOrderSearchSqlParameters(criteria);

            Array.Resize(ref parameters, parameters.Length + 5);
            parameters[parameters.Length - 5] = new SqlParameter("@IsEntruck", SqlDbType.Bit);
            if (!criteria.IsEntruck.HasValue)
            {
                parameters[parameters.Length - 5].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 5].Value = criteria.IsEntruck.Value;
            }
            parameters[parameters.Length - 4] = new SqlParameter("@PlannedShippingDateStart", SqlDbType.DateTime);
            if (criteria.PlannedShippingDate == null || !criteria.PlannedShippingDate.Start.HasValue ||
                criteria.PlannedShippingDate.Start == DateTime.MinValue || criteria.PlannedShippingDate.Start == DateTime.MaxValue)
            {
                parameters[parameters.Length - 4].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 4].Value = criteria.PlannedShippingDate.Start.Value;
            }

            parameters[parameters.Length - 3] = new SqlParameter("@PlannedShippingDateEnd", SqlDbType.DateTime);
            if (criteria.PlannedShippingDate == null || !criteria.PlannedShippingDate.End.HasValue ||
                criteria.PlannedShippingDate.End == DateTime.MinValue || criteria.PlannedShippingDate.End == DateTime.MaxValue)
            {
                parameters[parameters.Length - 3].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 3].Value = criteria.PlannedShippingDate.End.Value;
            }


            parameters[parameters.Length - 2] = new SqlParameter("@OnsiteOperator", SqlDbType.UniqueIdentifier);
            if (!criteria.OnsiteOperator.HasValue || criteria.OnsiteOperator == Guid.Empty)
            {
                parameters[parameters.Length - 2].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 2].Value = criteria.OnsiteOperator;
            }

            parameters[parameters.Length - 1] = new SqlParameter("@IsGoodsJilted", SqlDbType.Bit);
            if (!criteria.IsGoodsJited.HasValue)
            {
                parameters[parameters.Length - 1].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 1].Value = criteria.IsGoodsJited.Value;
            }

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryOnsiteEntrucking, parameters, toReturn, null);

            return toReturn;


        }

        public System.Data.DataTable QueryShuntingTrackInfoQuery(IssuingOrderSearchCriteria criteria, string tableName)
        {

            var parameters = GetIssuingOrderSearchSqlParameters(criteria);

            Array.Resize(ref parameters, parameters.Length + 4);
            parameters[parameters.Length - 4] = new SqlParameter("@IsShunted", SqlDbType.Bit);
            if (!criteria.IsShunted.HasValue)
            {
                parameters[parameters.Length - 4].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 4].Value = criteria.IsShunted.Value;
            }
            parameters[parameters.Length - 3] = new SqlParameter("@PlannedShippingDateStart", SqlDbType.DateTime);
            if (criteria.PlannedShippingDate == null || !criteria.PlannedShippingDate.Start.HasValue ||
                criteria.PlannedShippingDate.Start == DateTime.MinValue || criteria.PlannedShippingDate.Start == DateTime.MaxValue)
            {
                parameters[parameters.Length - 3].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 3].Value = criteria.PlannedShippingDate.Start.Value;
            }

            parameters[parameters.Length - 2] = new SqlParameter("@PlannedShippingDateEnd", SqlDbType.DateTime);
            if (criteria.PlannedShippingDate == null || !criteria.PlannedShippingDate.End.HasValue ||
                criteria.PlannedShippingDate.End == DateTime.MinValue || criteria.PlannedShippingDate.End == DateTime.MaxValue)
            {
                parameters[parameters.Length - 2].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 2].Value = criteria.PlannedShippingDate.End.Value;
            }


            parameters[parameters.Length - 1] = new SqlParameter("@ShuntingPerson", SqlDbType.UniqueIdentifier);
            if (!criteria.ShuntingPerson.HasValue || criteria.ShuntingPerson == Guid.Empty)
            {
                parameters[parameters.Length - 1].Value = DBNull.Value;
            }
            else
            {
                parameters[parameters.Length - 1].Value = criteria.ShuntingPerson;
            }

           

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryShuntingTrackInfoQuery, parameters, toReturn, null);

            return toReturn;

        }

        public System.Data.DataTable QueryIsuingOrderQueryDelivery(IssuingOrderSearchCriteria criteria, string tableName)
        {

            var parameters = GetIssuingOrderSearchSqlParameters(criteria);

            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryIssuingOrderDeliveryQuery, parameters, toReturn, null);

            return toReturn;


        }
        private static SqlParameter[] GetIssuingOrderSearchSqlParameters(IssuingOrderSearchCriteria criteria)
        {
            SqlParameter[] parameters = new SqlParameter[10];
            parameters[0] = new SqlParameter("@OrderCode", SqlDbType.VarChar, 50);
            if (string.IsNullOrWhiteSpace(criteria.OrderCode))
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = criteria.OrderCode;
            }

            parameters[1] = new SqlParameter("@CustomerId", SqlDbType.UniqueIdentifier);
            if (!criteria.CustomerId.HasValue || criteria.CustomerId == Guid.Empty)
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = criteria.CustomerId;
            }

            parameters[2] = new SqlParameter("@TransportationPlanCode", SqlDbType.VarChar, 50);
            if (string.IsNullOrWhiteSpace(criteria.TransportationPlanCode))
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = criteria.TransportationPlanCode;
            }
            parameters[3] = new SqlParameter("@OrderStatus", SqlDbType.Int);
            if (!criteria.OrderStatus.HasValue)
            {
                parameters[3].Value = DBNull.Value;
            }
            else
            {
                parameters[3].Value = criteria.OrderStatus;
            }

            parameters[4] = new SqlParameter("@RouteTypeId", SqlDbType.UniqueIdentifier);
            if (!criteria.RouteTypeId.HasValue || criteria.RouteTypeId == Guid.Empty)
            {
                parameters[4].Value = DBNull.Value;
            }
            else
            {
                parameters[4].Value = criteria.RouteTypeId;
            }

            parameters[5] = new SqlParameter("@ReceivingOrderDateStart", SqlDbType.DateTime);
            if (criteria.ReceivingOrderDate == null || !criteria.ReceivingOrderDate.Start.HasValue ||
                criteria.ReceivingOrderDate.Start == DateTime.MinValue || criteria.ReceivingOrderDate.Start == DateTime.MaxValue)
            {
                parameters[5].Value = DBNull.Value;
            }
            else
            {
                parameters[5].Value = criteria.ReceivingOrderDate.Start.Value;
            }

            parameters[6] = new SqlParameter("@ReceivingOrderDateEnd", SqlDbType.DateTime);
            if (criteria.ReceivingOrderDate == null || !criteria.ReceivingOrderDate.End.HasValue ||
                criteria.ReceivingOrderDate.End == DateTime.MinValue || criteria.ReceivingOrderDate.End == DateTime.MaxValue)
            {
                parameters[6].Value = DBNull.Value;
            }
            else
            {
                parameters[6].Value = criteria.ReceivingOrderDate.End.Value;
            }


            parameters[7] = new SqlParameter("@GoodsShippedTypeId", SqlDbType.UniqueIdentifier);
            if (!criteria.GoodsShippedTypeId.HasValue || criteria.GoodsShippedTypeId == Guid.Empty)
            {
                parameters[7].Value = DBNull.Value;
            }
            else
            {
                parameters[7].Value = criteria.GoodsShippedTypeId.Value;
            }

            parameters[8] = new SqlParameter("@DeliveryCity", SqlDbType.NChar, 6);
            if (string.IsNullOrWhiteSpace(criteria.DeliveryCity))
            {
                parameters[8].Value = DBNull.Value;
            }
            else
            {
                parameters[8].Value = criteria.DeliveryCity;
            }

            parameters[9] = new SqlParameter("@AreaId", SqlDbType.UniqueIdentifier);
            if (!criteria.AreaId.HasValue || criteria.AreaId == Guid.Empty)
            {
                parameters[9].Value = DBNull.Value;
            }
            else
            {
                parameters[9].Value = criteria.AreaId;
            }
            
            return parameters;
        }




        private static SqlParameter[] GetReceiptInfoExceptionQuerySqlParameters(ReceiptInfoQueryCriteria criteria)
        {


            return GetQueryReceiptInfoQuerySqlParameters(criteria);
        }





        private static SqlParameter[] GetGoodsShippingStatusQuerySqlParameters(GoodsShippingStatusQueryCriteria criteria)
        {
            SqlParameter[] parameters = new SqlParameter[6];

            parameters[0] = new SqlParameter("@RouteTypeId", SqlDbType.UniqueIdentifier);
            if (!criteria.RouteTypeId.HasValue || criteria.RouteTypeId == Guid.Empty)
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = criteria.RouteTypeId;
            }

            parameters[1] = new SqlParameter("@AreaId", SqlDbType.UniqueIdentifier);
            if (!criteria.AreaId.HasValue || criteria.AreaId == Guid.Empty)
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = criteria.AreaId;
            }

            parameters[2] = new SqlParameter("@ActualDeliveredDateStart", SqlDbType.DateTime);
            if (criteria.ActualDeliveredDate == null || !criteria.ActualDeliveredDate.Start.HasValue ||
               criteria.ActualDeliveredDate.Start == DateTime.MinValue || criteria.ActualDeliveredDate.Start == DateTime.MaxValue)
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = criteria.ActualDeliveredDate.Start.Value;
            }

            parameters[3] = new SqlParameter("@ActualDeliveredDateEnd", SqlDbType.DateTime);
            if (criteria.ActualDeliveredDate == null || !criteria.ActualDeliveredDate.End.HasValue ||
                criteria.ActualDeliveredDate.End == DateTime.MinValue || criteria.ActualDeliveredDate.End == DateTime.MaxValue)
            {
                parameters[3].Value = DBNull.Value;
            }
            else
            {
                parameters[3].Value = criteria.ActualDeliveredDate.End.Value;
            }


            parameters[4] = new SqlParameter("@ActualShippingDateStart", SqlDbType.DateTime);
            if (criteria.ActualShippingDate == null || !criteria.ActualShippingDate.Start.HasValue ||
               criteria.ActualShippingDate.Start == DateTime.MinValue || criteria.ActualShippingDate.Start == DateTime.MaxValue)
            {
                parameters[4].Value = DBNull.Value;
            }
            else
            {
                parameters[4].Value = criteria.ActualShippingDate.Start.Value;
            }

            parameters[5] = new SqlParameter("@ActualShippingDateEnd", SqlDbType.DateTime);
            if (criteria.ActualShippingDate == null || !criteria.ActualShippingDate.End.HasValue ||
                criteria.ActualShippingDate.End == DateTime.MinValue || criteria.ActualShippingDate.End == DateTime.MaxValue)
            {
                parameters[5].Value = DBNull.Value;
            }
            else
            {
                parameters[5].Value = criteria.ActualShippingDate.End.Value;
            }

          
            return parameters;
        }

        private static SqlParameter[] GetQueryReceiptInfoQuerySqlParameters(ReceiptInfoQueryCriteria criteria)
        {
            SqlParameter[] parameters = new SqlParameter[8];

            parameters[0] = new SqlParameter("@RouteTypeId", SqlDbType.UniqueIdentifier);
            if (!criteria.RouteTypeId.HasValue || criteria.RouteTypeId == Guid.Empty)
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = criteria.RouteTypeId;
            }

            parameters[1] = new SqlParameter("@AreaId", SqlDbType.UniqueIdentifier);
            if (!criteria.AreaId.HasValue || criteria.AreaId == Guid.Empty)
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = criteria.AreaId;
            }

            parameters[2] = new SqlParameter("@ReceiptInfoPlannedResponseDateStart", SqlDbType.DateTime);
            if (criteria.ReceiptInfoPlannedResponseDate == null || !criteria.ReceiptInfoPlannedResponseDate.Start.HasValue ||
               criteria.ReceiptInfoPlannedResponseDate.Start == DateTime.MinValue || criteria.ReceiptInfoPlannedResponseDate.Start == DateTime.MaxValue)
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = criteria.ReceiptInfoPlannedResponseDate.Start.Value;
            }

            parameters[3] = new SqlParameter("@ReceiptInfoPlannedResponseDateEnd", SqlDbType.DateTime);
            if (criteria.ReceiptInfoPlannedResponseDate == null || !criteria.ReceiptInfoPlannedResponseDate.End.HasValue ||
                criteria.ReceiptInfoPlannedResponseDate.End == DateTime.MinValue || criteria.ReceiptInfoPlannedResponseDate.End == DateTime.MaxValue)
            {
                parameters[3].Value = DBNull.Value;
            }
            else
            {
                parameters[3].Value = criteria.ReceiptInfoPlannedResponseDate.End.Value;
            }

            parameters[4] = new SqlParameter("@ActualShippingDateStart", SqlDbType.DateTime);
            if (criteria.ActualShippingDate == null || !criteria.ActualShippingDate.Start.HasValue ||
               criteria.ActualShippingDate.Start == DateTime.MinValue || criteria.ActualShippingDate.Start == DateTime.MaxValue)
            {
                parameters[4].Value = DBNull.Value;
            }
            else
            {
                parameters[4].Value = criteria.ActualShippingDate.Start.Value;
            }

            parameters[5] = new SqlParameter("@ActualShippingDateEnd", SqlDbType.DateTime);
            if (criteria.ActualShippingDate == null || !criteria.ActualShippingDate.End.HasValue ||
                criteria.ActualShippingDate.End == DateTime.MinValue || criteria.ActualShippingDate.End == DateTime.MaxValue)
            {
                parameters[5].Value = DBNull.Value;
            }
            else
            {
                parameters[5].Value = criteria.ActualShippingDate.End.Value;
            }
            //回单日期
            parameters[6] = new SqlParameter("@ReceivingOrderDateStart", SqlDbType.DateTime);
            if (criteria.ReceivingOrderDate == null || !criteria.ReceivingOrderDate.Start.HasValue ||
               criteria.ReceivingOrderDate.Start == DateTime.MinValue || criteria.ReceivingOrderDate.Start == DateTime.MaxValue)
            {
                parameters[6].Value = DBNull.Value;
            }
            else
            {
                parameters[6].Value = criteria.ReceivingOrderDate.Start.Value;
            }

            parameters[7] = new SqlParameter("@ReceivingOrderDateEnd", SqlDbType.DateTime);
            if (criteria.ReceivingOrderDate == null || !criteria.ReceivingOrderDate.End.HasValue ||
                criteria.ReceivingOrderDate.End == DateTime.MinValue || criteria.ReceivingOrderDate.End == DateTime.MaxValue)
            {
                parameters[7].Value = DBNull.Value;
            }
            else
            {
                parameters[7].Value = criteria.ReceivingOrderDate.End.Value;
            }

            return parameters;
        }

        private static SqlParameter[] GetQueryGoodsShippingExceptionSqlParameters(GoodsShippingExceptionQueryCriteria criteria)
        {
            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@RouteTypeId", SqlDbType.UniqueIdentifier);
            if (!criteria.RouteTypeId.HasValue || criteria.RouteTypeId == Guid.Empty)
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = criteria.RouteTypeId;
            }

            parameters[1] = new SqlParameter("@AreaId", SqlDbType.UniqueIdentifier);
            if (!criteria.AreaId.HasValue || criteria.AreaId == Guid.Empty)
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = criteria.AreaId;
            }
            return parameters;
        }


        private static SqlParameter[] GetPaymentAccountQuerySearchSqlParameters(MachineAccountSearchReportCriteria criteria)
        {
            SqlParameter[] parameters = new SqlParameter[12];
            parameters[0] = new SqlParameter("@OrderCode", SqlDbType.VarChar, 50);
            if (string.IsNullOrWhiteSpace(criteria.OrderCode))
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = criteria.OrderCode;
            }


            parameters[1] = new SqlParameter("@TransportationPlanCode", SqlDbType.VarChar, 50);
            if (string.IsNullOrWhiteSpace(criteria.TransportationPlanCode))
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = criteria.TransportationPlanCode;
            }

            parameters[2] = new SqlParameter("@RouteTypeId", SqlDbType.UniqueIdentifier);
            if (!criteria.RouteTypeId.HasValue || criteria.RouteTypeId == Guid.Empty)
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = criteria.RouteTypeId;
            }

            parameters[3] = new SqlParameter("@PaymentOrIncomeDateStart", SqlDbType.DateTime);
            if (criteria.PaymentOrIncomeDate == null || !criteria.PaymentOrIncomeDate.Start.HasValue ||
                criteria.PaymentOrIncomeDate.Start == DateTime.MinValue || criteria.PaymentOrIncomeDate.Start == DateTime.MaxValue)
            {
                parameters[3].Value = DBNull.Value;
            }
            else
            {
                parameters[3].Value = criteria.PaymentOrIncomeDate.Start.Value;
            }

            parameters[4] = new SqlParameter("@PaymentOrIncomeDateEnd", SqlDbType.DateTime);
            if (criteria.PaymentOrIncomeDate == null || !criteria.PaymentOrIncomeDate.End.HasValue ||
                criteria.PaymentOrIncomeDate.End == DateTime.MinValue || criteria.PaymentOrIncomeDate.End == DateTime.MaxValue)
            {
                parameters[4].Value = DBNull.Value;
            }
            else
            {
                parameters[4].Value = criteria.PaymentOrIncomeDate.End.Value;
            }


            parameters[5] = new SqlParameter("@PaymentMethod", SqlDbType.UniqueIdentifier);
            if (!criteria.PaymentMethod.HasValue || criteria.PaymentMethod == Guid.Empty)
            {
                parameters[5].Value = DBNull.Value;
            }
            else
            {
                parameters[5].Value = criteria.PaymentMethod.Value;
            }


            parameters[6] = new SqlParameter("@Driver", SqlDbType.VarChar, 50);
            if (string.IsNullOrWhiteSpace(criteria.Driver))
            {
                parameters[6].Value = DBNull.Value;
            }
            else
            {
                parameters[6].Value = criteria.Driver;
            }


            parameters[7] = new SqlParameter("@ActualShippingDateStart", SqlDbType.DateTime);
            if (criteria.ActualShippingDate == null || !criteria.ActualShippingDate.Start.HasValue ||
                criteria.ActualShippingDate.Start == DateTime.MinValue || criteria.ActualShippingDate.Start == DateTime.MaxValue)
            {
                parameters[7].Value = DBNull.Value;
            }
            else
            {
                parameters[7].Value = criteria.ActualShippingDate.Start.Value;
            }

            parameters[8] = new SqlParameter("@ActualShippingDateEnd", SqlDbType.DateTime);
            if (criteria.ActualShippingDate == null || !criteria.ActualShippingDate.End.HasValue ||
                criteria.ActualShippingDate.End == DateTime.MinValue || criteria.ActualShippingDate.End == DateTime.MaxValue)
            {
                parameters[8].Value = DBNull.Value;
            }
            else
            {
                parameters[8].Value = criteria.ActualShippingDate.End.Value;
            }


            parameters[9] = new SqlParameter("@PendingShippingCostStart", SqlDbType.Decimal);
            if (!criteria.PendingShippingCostStart.HasValue)
            {
                parameters[9].Value = DBNull.Value;
            }
            else
            {
                parameters[9].Value = criteria.PendingShippingCostStart.Value;
            }

            parameters[10] = new SqlParameter("@PendingShippingCostEnd", SqlDbType.Decimal);
            if (!criteria.PendingShippingCostEnd.HasValue)
            {
                parameters[10].Value = DBNull.Value;
            }
            else
            {
                parameters[10].Value = criteria.PendingShippingCostEnd.Value;
            }


            parameters[11] = new SqlParameter("@AreaId", SqlDbType.UniqueIdentifier);
            if (!criteria.AreaId.HasValue || criteria.AreaId == Guid.Empty)
            {
                parameters[11].Value = DBNull.Value;
            }
            else
            {
                parameters[11].Value = criteria.AreaId;
            }
            return parameters;
        }

        private static SqlParameter[] GetPaymentAccountDetailsQuerySearchSqlParameters(MachineAccountSearchReportCriteria criteria)
        {
            SqlParameter[] parameters = new SqlParameter[3];
            

            parameters[0] = new SqlParameter("@PaymentOrIncomeDateStart", SqlDbType.DateTime);
            if (criteria.PaymentOrIncomeDate == null || !criteria.PaymentOrIncomeDate.Start.HasValue ||
                criteria.PaymentOrIncomeDate.Start == DateTime.MinValue || criteria.PaymentOrIncomeDate.Start == DateTime.MaxValue)
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = criteria.PaymentOrIncomeDate.Start.Value;
            }

            parameters[1] = new SqlParameter("@PaymentOrIncomeDateEnd", SqlDbType.DateTime);
            if (criteria.PaymentOrIncomeDate == null || !criteria.PaymentOrIncomeDate.End.HasValue ||
                criteria.PaymentOrIncomeDate.End == DateTime.MinValue || criteria.PaymentOrIncomeDate.End == DateTime.MaxValue)
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = criteria.PaymentOrIncomeDate.End.Value;
            }




            parameters[2] = new SqlParameter("@IssuingOrderId", SqlDbType.UniqueIdentifier);
            if (!criteria.IssuingOrderId.HasValue || criteria.IssuingOrderId == Guid.Empty)
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = criteria.IssuingOrderId;
            }
            return parameters;
        }
        private static SqlParameter[] GetQueryPaymentMachineAccountDeductionSearchSqlParameters(MachineAccountSearchCriteria criteria)
        {
            SqlParameter[] parameters = new SqlParameter[8];
            parameters[0] = new SqlParameter("@Driver", SqlDbType.VarChar, 50);
            if (string.IsNullOrWhiteSpace(criteria.Driver))
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = criteria.Driver;
            }

            parameters[1] = new SqlParameter("@RouteTypeId", SqlDbType.UniqueIdentifier);
            if (!criteria.RouteTypeId.HasValue || criteria.RouteTypeId == Guid.Empty)
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = criteria.RouteTypeId;
            }

            parameters[2] = new SqlParameter("@PaymentOrIncomeDateStart", SqlDbType.DateTime);
            if (criteria.PaymentOrIncomeDate == null || !criteria.PaymentOrIncomeDate.Start.HasValue ||
                criteria.PaymentOrIncomeDate.Start == DateTime.MinValue || criteria.PaymentOrIncomeDate.Start == DateTime.MaxValue)
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = criteria.PaymentOrIncomeDate.Start.Value;
            }

            parameters[3] = new SqlParameter("@PaymentOrIncomeDateEnd", SqlDbType.DateTime);
            if (criteria.PaymentOrIncomeDate == null || !criteria.PaymentOrIncomeDate.End.HasValue ||
                criteria.PaymentOrIncomeDate.End == DateTime.MinValue || criteria.PaymentOrIncomeDate.End == DateTime.MaxValue)
            {
                parameters[3].Value = DBNull.Value;
            }
            else
            {
                parameters[3].Value = criteria.PaymentOrIncomeDate.End.Value;
            }


            parameters[4] = new SqlParameter("@PaymentMethod", SqlDbType.UniqueIdentifier);
            if (!criteria.PaymentMethod.HasValue || criteria.PaymentMethod == Guid.Empty)
            {
                parameters[4].Value = DBNull.Value;
            }
            else
            {
                parameters[4].Value = criteria.PaymentMethod.Value;
            }


            parameters[5] = new SqlParameter("@DeductionAmountStart", SqlDbType.Decimal);
            if (!criteria.DeductionAmountStart.HasValue )
            {
                parameters[5].Value = DBNull.Value;
            }
            else
            {
                parameters[5].Value = criteria.DeductionAmountStart.Value;
            }

            parameters[6] = new SqlParameter("@DeductionAmountEnd", SqlDbType.Decimal);
            if (!criteria.DeductionAmountEnd.HasValue)
            {
                parameters[6].Value = DBNull.Value;
            }
            else
            {
                parameters[6].Value = criteria.DeductionAmountEnd.Value;
            }

            parameters[7] = new SqlParameter("@AreaId", SqlDbType.UniqueIdentifier);
            if (!criteria.AreaId.HasValue || criteria.AreaId == Guid.Empty)
            {
                parameters[7].Value = DBNull.Value;
            }
            else
            {
                parameters[7].Value = criteria.AreaId;
            }
            return parameters;
        }
        private T[] CombineArray<T>(T[] objOne, T[] objTwo)
        {
            T[] results = null;
            if (objOne == null && objTwo == null)
            {
                return results;
            }
            if (objOne == null)
            {
                return objTwo;
            }
            if (objTwo == null)
            {
                return objOne;
            }

            List<T> arrayList = new List<T>();
            arrayList.AddRange(objOne);
            arrayList.AddRange(objTwo);
            results = arrayList.ToArray();
            return results;
        }


        /// <summary>
        /// JamesRen
        /// 2013-02-26
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public System.Data.DataTable QueryIssuingTotalOrder(IIssuingTotalOrder criteria, string tableName)
        {

            var parameters = GetIssuingTotalOrderSearchSqlParameters(criteria);

           
            DataTable toReturn = new DataTable(tableName);
            DbUtils.CallRetrievalStoredProcedure(SP_QueryIssuingOrderGather, parameters, toReturn, null);

            return toReturn;


        }

        /// <summary>
        /// JamesRen
        /// 2013-02-26
        /// </summary>
        /// <param name="criteria"></param>
        /// <returns></returns>
        private static SqlParameter[] GetIssuingTotalOrderSearchSqlParameters(IIssuingTotalOrder criteria)
        {
            SqlParameter[] parameters = new SqlParameter[5];
            
            parameters[0] = new SqlParameter("@StartDate", SqlDbType.NVarChar);
            if (criteria.TimeQuantum == null || !criteria.TimeQuantum.Start.HasValue ||
                criteria.TimeQuantum.Start == DateTime.MinValue || criteria.TimeQuantum.Start == DateTime.MaxValue)
            {
                parameters[0].Value = DBNull.Value;
            }
            else
            {
                parameters[0].Value = criteria.TimeQuantum.Start.Value.ToString("yyyy-MM-dd");
            }

            parameters[1] = new SqlParameter("@EndDate", SqlDbType.NVarChar);
            if (criteria.TimeQuantum == null || !criteria.TimeQuantum.End.HasValue ||
                criteria.TimeQuantum.End == DateTime.MinValue || criteria.TimeQuantum.End == DateTime.MaxValue)
            {
                parameters[1].Value = DBNull.Value;
            }
            else
            {
                parameters[1].Value = criteria.TimeQuantum.End.Value.ToString("yyyy-MM-dd");
            }

            //线路ID
            parameters[2] = new SqlParameter("@RouteTypeId", SqlDbType.UniqueIdentifier);
            if (!criteria.RouteTypeId.HasValue || criteria.RouteTypeId == Guid.Empty)
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = criteria.RouteTypeId;
            }
            //承运货物
            parameters[3] = new SqlParameter("@GoodsShippedTypeId", SqlDbType.UniqueIdentifier);
            if (!criteria.GoodsShippedTypeId.HasValue || criteria.GoodsShippedTypeId == Guid.Empty)
            {
                parameters[3].Value = DBNull.Value;
            }
            else
            {
                parameters[3].Value = criteria.GoodsShippedTypeId.Value;
            }

            //客户ID
            parameters[4] = new SqlParameter("@CustomerId", SqlDbType.UniqueIdentifier);
            if (!criteria.CustomerId.HasValue || criteria.CustomerId == Guid.Empty)
            {
                parameters[4].Value = DBNull.Value;
            }
            else
            {
                parameters[4].Value = criteria.CustomerId;
            }
           

          
            return parameters;
        }

    }


}
